package com.zzb.codegenerator.generator.dctm.sqlserver;

import com.zzb.codegenerator.bean.TableColumn;
import com.zzb.codegenerator.bean.TableMeta;
import com.zzb.codegenerator.config.GeneratorConfig;
import com.zzb.codegenerator.generator.connect.ConnectPool;
import com.zzb.codegenerator.generator.context.TableMetaContext;
import com.zzb.codegenerator.generator.dctm.DbToTableMetaConvertor;
import com.zzb.codegenerator.generator.table.ITableMetaFoctory;
import com.zzb.codegenerator.utils.GetJdbcUrlUtil;
import com.zzb.codegenerator.utils.OperatorEnvUtils;
import com.zzb.codegenerator.utils.SQLServerTypeToJavaType;
import org.apache.commons.lang3.StringUtils;
import org.joda.time.DateTime;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by ZZB on 2017/12/4.
 */
public class SQLServerToTableMetaConvertor implements DbToTableMetaConvertor {
    private static final String sqlserverTableMetaSQL =
            "SELECT\n" +
                    "  sys.columns.name as columnName,\n" +
                    "  UPPER(sys.types.name) as columnType,\n" +
                    "  sys.columns.max_length as maxLength,\n" +
                    "  sys.columns.precision as precision,\n" +
                    "  sys.columns.scale as scale,\n" +
                    "  sys.columns.is_nullable as isNullable\n" +
                    "FROM sys.columns, sys.tables, sys.types ,sys.schemas\n" +
                    "WHERE sys.columns.object_id = sys.tables.object_id AND sys.columns.user_type_id = sys.types.user_type_id AND sys.schemas" +
                    ".schema_id = sys.tables.schema_id\n" +
                    "      and sys.tables.name = '{tableName}'\n" +
                    "      and sys.schemas.name = '{schemaName}'\n" +
                    "ORDER BY sys.columns.column_id asc;";
    private static final String sqlserverTableMetaPrimarySQL =
            "SELECT COLUMN_NAME as colname FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n" +
                    "WHERE TABLE_NAME='{tableName}' and  CONSTRAINT_SCHEMA = '{schemaName}'";


    private GeneratorConfig generatorConfig;

    public SQLServerToTableMetaConvertor(GeneratorConfig generatorConfig) {
        this.generatorConfig = generatorConfig;
    }

    private static String buildSQLServerFullTypeName(
            String typeName, Long maxLength, Long precision, Long scale) {
        if ("CHAR".equals(typeName.toUpperCase()) || "VARCHAR".equals(typeName.toUpperCase()) || "NVARCHAR".equals(typeName.toUpperCase()))
            if (maxLength != null && maxLength != 0) {
                typeName = typeName + "(" + maxLength + ")";
            }
        if ("DECIMAL".equals(typeName.toUpperCase()) || "NUMERIC".equals(typeName.toUpperCase()))
            if (precision != null && precision != 0) {
                if (scale != null && scale != 0) {
                    typeName = typeName + "(" + precision + "," + scale + ")";
                } else {
                    typeName = typeName + "(" + precision + ")";
                }
            }


        return typeName;
    }

    @Override
    public TableMeta convertFromTableName(String tableFullName) throws Exception {
        TableMeta tableMeta = new TableMeta();
        tableMeta.setTableName(tableFullName);
        List<TableColumn> list = new ArrayList<>();

        //获取连接
        Connection connection = ConnectPool.getConnection(GetJdbcUrlUtil.getDriverName(generatorConfig.getJdbcUrl()),
                generatorConfig.getJdbcUrl(), generatorConfig.getJdbcUsername(), generatorConfig.getJdbcPassword());
        if (connection == null) throw new Exception("数据库连接失败");
        if (!tableExist(connection, tableFullName)) return null;
        String[] tableNameSplits = tableFullName.split("\\.");
        String schema = null;
        String tableName = null;
        if (tableNameSplits.length == 1) {
            schema = "dbo";
            tableName = tableNameSplits[0];
        } else {
            schema = tableNameSplits[0];
            tableName = tableNameSplits[1];
        }
        String pkColumnName = null;
        if (StringUtils.isBlank(generatorConfig.getPrimaryKeys())) {
            pkColumnName = getPrimaryKeyColumn(tableName, schema, connection);
        } else {
            pkColumnName =  TableMetaContext.getTablePkMap().get(tableName);
        }
        String sql = sqlserverTableMetaSQL.replace("{tableName}", tableName)
                .replace("{schemaName}", schema);
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        tableMeta.setPrimaryKeyColumns(new ArrayList<>());
        while (rs.next()) {
            TableColumn tableColumn = new TableColumn();
            tableColumn.setColumnName(rs.getString("columnName"));
            tableColumn.setColumnType(rs.getString("columnType"));
            tableColumn.setMaxLength(rs.getLong("maxLength"));
            tableColumn.setPrecision(rs.getLong("precision"));
            tableColumn.setScale(rs.getLong("scale"));
            tableColumn.setNullable(rs.getBoolean("isNullable"));
            tableColumn.setUnsigned(false);
            tableColumn.setDefaultValue("");
            tableColumn.setPrimaryKey(false);

            if (tableColumn.getDefaultValue() != null) {
                if (tableColumn.getDefaultValue().toLowerCase().startsWith("nextval('")
                        && tableColumn.getDefaultValue().toLowerCase().endsWith("serial_seq'::regclass)")) {
                    tableColumn.setDefaultValue("");
                }
            }
            tableColumn.setColumnTypeFullName(buildSQLServerFullTypeName(tableColumn.getColumnType(), tableColumn.getMaxLength(),
                    tableColumn.getPrecision(), tableColumn.getScale()));
            try {
                tableColumn.setPropertyType(SQLServerTypeToJavaType.sqlServer2Java.get(tableColumn.getColumnType()).getJavaType());
                tableColumn.setPropertyFullType(SQLServerTypeToJavaType.sqlServer2Java.get(tableColumn.getColumnType()).getJavaFullType());
                tableColumn.setJdbcType(SQLServerTypeToJavaType.sqlServer2Java.get(tableColumn.getColumnType()).getSqlserverType().getName());
            } catch (Exception e) {
                System.out.println("类型转换异常！[{name}]-[{type}]"
                        .replace("{name}", tableColumn.getColumnName())
                        .replace("{type}", tableColumn.getColumnType()));
            }
            tableColumn.setPropertyName(ITableMetaFoctory.rename(generatorConfig.getNameType(), tableColumn.getColumnName()));
            list.add(tableColumn);
            if (StringUtils.containsIgnoreCase( pkColumnName,tableColumn.getColumnName())) {
                tableColumn.setPrimaryKey(true);
                tableMeta.getPrimaryKeyColumns().add(tableColumn);
            }
        }
        tableMeta.setTableColumns(list);
        tableMeta.setConfig(generatorConfig);
        tableMeta.setTableName(tableName);
        tableMeta.setBeanName(TableMetaContext.getTableMap().get(tableName));
        tableMeta.setTableComment("");
        tableMeta.setPrimaryKeyName(pkColumnName);
        tableMeta.setSystemUserName(OperatorEnvUtils.getSystemUserName());
        tableMeta.setCurrentTime(new Date());
        return tableMeta;
    }

    @Override
    public boolean tableExist(Connection connection, String tableName) {
        try {
            Statement statement = connection.createStatement();
            System.out.println("查询开始 " +tableName +" " + DateTime.now().toString("yyyy-MM-dd HH:mm:ss"));
            ResultSet resultSet = statement.executeQuery("select top 1 1 from [" + tableName + "] nolock");
            resultSet.next();
            resultSet.close();
            statement.close();
            System.out.println("查询结束 " +tableName +" " + DateTime.now().toString("yyyy-MM-dd HH:mm:ss"));
            return true;
        } catch (SQLException e) {
            return false;
        }
    }

    private String getPrimaryKeyColumn(String tableName, String schema, Connection connection) throws SQLException {
        String primarySQL = sqlserverTableMetaPrimarySQL.replace("{tableName}", tableName).replace("{schemaName}", schema);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(primarySQL);
        List<String> primaryKeys = new ArrayList<>();
        while (resultSet.next()) {
            String colname = resultSet.getString("colname");
            primaryKeys.add(colname);
        }

        resultSet.close();
        statement.close();
        return primaryKeys.stream().reduce((a,b)->a+","+b).orElse("id");
    }

}
